---
title: Stored-procedure read models
summary: Retrieves claim-related calculated values from Oracle stored procedures via REF CURSOR results.
status: draft
owner: SAIF Documentation Generator
last_reviewed: 2026-08-21
library: SAIF.Business.Claim
reference_type: embedded
operation_id: stored-proc-lookups
trigger: Caller retrieves aggravation-rights dates, timeliness of first payment, medically-stationary date, or statutory weekly wage
---

# Stored-procedure read models

Provides read-only, stored-procedure-backed lookups for aggravation-rights dates, timeliness of first payment, medically-stationary date, and statutory weekly wage calculations, each returning a single REF CURSOR result set.

| Field | Value |
| --- | --- |
| **Operation ID** | stored-proc-lookups |
| **Trigger** | Caller retrieves one of the four read-only business objects |
| **Inputs** | ClaimID and calculation-specific inputs (e.g., injury date, wages, pay period) |
| **Outputs** | Calculated result values populated in `AfterRetrieve` |
| **Primary components** | `clsAggRightDates`, `clsTimelinessOfFirstPayment`, `clsMedicallyStationaryDate`, `clsStatutoryWeeklyWageCalculation` |
| **Entry point** | SAIF.Business.Claim/clsAggRightDates.vb |

## Steps

1. `clsAggRightDates` calls `sp82046_CalcAggRightDates` with `inClaimID` and a REF CURSOR output ([SQL-013](../sql/SQL-013.md)).
   - `AfterRetrieve` raises "Claim Not Found" when the cursor returns no rows ([BR-008](../business-rules.md#br-008-claim-not-found-on-aggravation-rights-retrieve)).
2. `clsTimelinessOfFirstPayment` calls `sp82067_TimelinessOf1stPayment` with `inClaimID` and an optional `requestType = 'Detail'` parameter added when `Criteria.FromClaimDetail` is true ([SQL-034](../sql/SQL-034.md)).
   - Reuses the same "Claim Not Found" error identifier as `clsAggRightDates` — an ID collision flagged as a defect.**
3. `clsMedicallyStationaryDate` calls `sp82289_medicallystationarydt` with `inSaifClaimNum` (length 7) and a REF CURSOR output ([SQL-025](../sql/SQL-025.md)).
4. `clsStatutoryWeeklyWageCalculation` calls `sp82286_statutoryweeklywage` with injury date, wages, pay period, hours worked per day, and days worked per week, plus a REF CURSOR output ([SQL-033](../sql/SQL-033.md)).
5. Each class populates its properties from the returned cursor in `AfterRetrieve`.

## Process flow

The following diagram visualizes this operation's control flow.

```mermaid
sequenceDiagram
	participant Caller
	participant ReadOnlyObject as Read-only business object
	participant Procedure as Oracle stored procedure
	Caller->>ReadOnlyObject: Retrieve(claimId, ...)
	ReadOnlyObject->>Procedure: CommandType.StoredProcedure call + REF CURSOR
	Procedure-->>ReadOnlyObject: cursor rows
	ReadOnlyObject->>ReadOnlyObject: AfterRetrieve populates properties
	ReadOnlyObject-->>Caller: populated object or "Claim Not Found"
```

## Failure and edge-case behavior
- All four procedures are invoked without a package qualifier, so package membership is unknown; procedure bodies reside in the Oracle database and could not be retrieved in this environment.**
- `clsAggRightDates` and `clsTimelinessOfFirstPayment` both raise "Claim Not Found" using the same reused identifier when no row is returned.**

## Related documentation
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts. Statements marked with `**` could not be confirmed; treat
> as unknown until verified.

## Sources
- SAIF.Business.Claim/clsAggRightDates.vb
- SAIF.Business.Claim/clsITimelinessOfFirstPayment.vb
- SAIF.Business.Claim/clsMedicallyStationaryDate.vb
- SAIF.Business.Claim/clsStatutoryWeeklyWageCalculation.vb